package com.mobilesales.android.database;

import java.util.ArrayList;
import java.util.List;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.util.Log;

import com.mobilesales.android.model.dictionary.Dictionary;
import com.mobilesales.android.model.dictionary.KeyValuePair;
import com.mobilesales.android.model.params.Param;
import com.mobilesales.android.model.products.Category;
import com.mobilesales.android.model.products.Product;
import com.mobilesales.android.model.products.ProductImage;
import com.mobilesales.android.model.products.Tax;
import com.mobilesales.android.model.products.Unit;


public class ProductManager extends ManagerBase{

	
	public ProductManager(Context c)
	{
		super(c);
	}
	
	public List<Product> GetProductList()
	{
     	try {
     		
     		String query = "SELECT _id"
     						+ " FROM Products";
     		Cursor c = db.rawQuery(query, null);
     		
     		List<Product> result = new ArrayList<Product>();
     		if (!c.moveToFirst())
     			return result;
     		
     		List<Integer> tmp = new ArrayList<Integer>();
     		while (!c.isAfterLast()){
     			tmp.add(c.getInt(c.getColumnIndex("_id")));
     			c.moveToNext();
     		}
     		c.close();
     		for (int id : tmp)
     			result.add(GetProduct(id));	
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	
		return null;
	}
	
	public Product GetProduct(int productId)
	{
     	try {
     		
     		String query = "SELECT _id, TaxId, UnitId, Sku, Name, Description, Price, Quantity, MinQuantity, OrderQuantity, Spread, Symb, MarketingOnly" 
							+ " FROM Products" 
							+ " WHERE _id = " + productId;
     		Cursor c = db.rawQuery(query, null);
     		

     		if (!c.moveToFirst())
     			return null;
     		
     		Product result = new Product();
     		result.Id = c.getInt(c.getColumnIndex("_id"));
     		result.Name = c.getString(c.getColumnIndex("Name"));
     		result.TaxId = c.getInt(c.getColumnIndex("TaxId"));
     		result.UnitId = c.getInt(c.getColumnIndex("UnitId"));
     		result.Sku = c.getString(c.getColumnIndex("Sku"));
     		result.Description = c.getString(c.getColumnIndex("Description"));
     		result.Price = c.getDouble(c.getColumnIndex("Price"));
     		result.Quantity = c.getDouble(c.getColumnIndex("Quantity"));
     		result.MinQuantity = c.getDouble(c.getColumnIndex("MinQuantity"));
     		result.OrderQuantity = c.getDouble(c.getColumnIndex("OrderQuantity"));
     		result.Spread = c.getDouble(c.getColumnIndex("Spread"));
     		result.Symbol = c.getString(c.getColumnIndex("Symb"));
     		result.IsMarketingOnlyItem = c.getInt(c.getColumnIndex("MarketingOnly")) == 1 ? true : false;    
     		
     		c.close();
     		
     		result.Params = GetProductParams(result.Id);
     		result.Categories = GetProductCategories(result.Id);
     		result.Images = GetProductImages(result.Id);
     		result.Tax = GetTax(result.TaxId);
     		result.Unit = GetUnit(result.UnitId);
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	
		return null;
	}
/*	public boolean RemoveProductImage(int productImageId)
	{
     	try {
     		int result;
			result = db.delete("ProductImage", "_id ="+productImageId, null);
			return result == 1 ? true : false; 
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     		return false;
     	}
	}*/
	public boolean RemoveProduct(int productId)
	{
     	try {
			if (db.delete("Products", "_id ="+productId, null) != 1)
				return false;
			
			db.delete("ProductHasParams", "ProductId = " + productId, null);
			return true;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     		return false;
     	}
	}
	
	public Dictionary<Param, String> GetProductParams(int productId)
	{
     	try {
     		
     		String query = "SELECT ParamId, Value"
	    			+ " FROM ProductHasParams" 
	    			+ " WHERE ProductId = " + productId;
     		Cursor c = db.rawQuery(query, null);
     		
     		Dictionary<Param, String> result = new Dictionary<Param,String>();
    		if (!c.moveToFirst())
    			return result;
    		ParamManager pm = new ParamManager(this.context);
     	
    		Dictionary<Integer, String> tmp = new Dictionary<Integer, String>();
	     	while (!c.isAfterLast()){    	     		
		     	KeyValuePair<Integer, String> p = new KeyValuePair<Integer, String>();
		     	p.setKey(c.getInt(c.getColumnIndex("ParamId")));
		     	p.setValue(c.getString(c.getColumnIndex("Value")));
		        tmp.add(p);
		        c.moveToNext();
	     	}
	     	
	     	c.close();
	     	for (KeyValuePair<Integer, String> kpv : tmp){
		     	KeyValuePair<Param, String> p = new KeyValuePair<Param, String>();
		     	p.setKey(pm.GetParam(kpv.getKey()));
		     	p.setValue(kpv.getValue());
		        result.add(p);
	     	}
	     	
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	
		return null;		
	}
	
	public boolean StoreProductParams(int productId, Dictionary<Param,String> paramMap)
	{
		if (paramMap == null || paramMap.size() == 0) return false;
     	try {
     		int result = 1;
     		String query = "SELECT ParamId"
    			+ " FROM ProductHasParams" 
    			+ " WHERE ProductId = " + productId;
	 		Cursor c = db.rawQuery(query, null);
	 		List<Integer> paramsId = new ArrayList<Integer>();
    		if (c.moveToFirst()){
	     		while (!c.isAfterLast()){    	     		
	         		paramsId.add(c.getInt(c.getColumnIndex("ParamId")));
	         		c.moveToNext();
	     		}
	     	}
	 		List<Integer> addedParams = new ArrayList<Integer>();
	 		for (KeyValuePair<Param, String> kpv : paramMap){
	 			addedParams.add(kpv.getKey().Id);
	 			boolean exists = paramsId.contains(kpv.getKey().Id);
	 			
				ContentValues cv = new ContentValues();
				
				cv.put("ProductId", productId);
				cv.put("ParamId",kpv.getKey().Id);
				cv.put("Value", kpv.getValue());
	
				if (exists)
					result = db.update("ProductHasParams", cv, "ProductId ="+productId + " and ParamId =" + kpv.getKey().Id, null);
				else
					result = db.insert("ProductHasParams", null, cv) != -1 ? 1 : 0;
				
				if (result != 1)
					throw new Exception("Failed to store product param + " + kpv.getKey().Id);
	 		}
	 		
	 		for (int paramId : paramsId)
	 		{
	 			if (!addedParams.contains(paramId))
		 	     	try {
		 				if (db.delete("ProductHasParams", "ProductId ="+productId + " and ParamId =" + paramId, null) <=0)
		 					throw new Exception("Faile to remove param row from ProductHasParams. ProductId" + productId + " ParamId " + paramId);
		 	     	}
		 	     	catch(SQLException sqle) {
		 	     		Log.d(this.getClass().getName() ,sqle.getMessage());
		 	     		throw sqle;	 
		 	     	}
	 		}
			return result == 1 ? true : false;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     		return false;
     	}	
	}
	
	public boolean StoreProduct(Product product)
	{
     	try {
     		int result;
			ContentValues cv = new ContentValues();

			cv.put("_id", product.Id);
			cv.put("Name",product.Name);
			cv.put("TaxId", product.TaxId);
			cv.put("UnitId", product.UnitId);
			cv.put("Sku", product.Sku);
			cv.put("Description", product.Description);
			cv.put("Price", product.Price);
			cv.put("Quantity", product.Quantity);
			cv.put("MinQuantity", product.MinQuantity);
			cv.put("OrderQuantity", product.OrderQuantity);
			cv.put("Spread", product.Spread);
			cv.put("Symb", product.Symbol);
			cv.put("MarketingOnly", product.IsMarketingOnlyItem);
			
			int productID;
			result = db.update("Products", cv, "_id ="+product.Id, null);
			productID = product.Id;
			if (result == 0){
				productID = (int)db.insert("Products", null, cv);
				result = productID == -1 ? -1 : 1;
			}
			
			if (result == -1)
				return false;
			
			if (!StoreProductParams(productID, product.Params))
				throw new Exception("Failed to store product params " + productID);
			if (!StoreProductCategories(productID, product.Categories))
				throw new Exception("Failed to store product categories " + productID);;
			for (ProductImage p : product.Images)
				if (!StoreProductImage(p))
					throw new Exception("Failed to store product image " + p.Id);;
			
			return result == 1 ? true : false;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     		return false;
     	}
	}
	
	public boolean StoreProductCategories(int productId, List<Category> categories)
	{
		if (categories == null || categories.size() == 0) return false;
     	try {
     		int result = 1;
     		String query = "SELECT CategoryId"
    			+ " FROM ProductHasCategories" 
    			+ " WHERE ProductId = " + productId;
	 		Cursor c = db.rawQuery(query, null);
	 		List<Integer> categoryIds = new ArrayList<Integer>();
    		if (c.moveToFirst()){
	     		while (!c.isAfterLast()){    	     		
	     			categoryIds.add(c.getInt(c.getColumnIndex("CategoryId")));
	         		c.moveToNext();
	     		}
	     	}
	 		List<Integer> addedCategories = new ArrayList<Integer>();
	 		for (Category cat : categories){
	 			addedCategories.add(cat.Id);
	 			
	 			if (!categoryIds.contains(cat.Id)){
					ContentValues cv = new ContentValues();
					
					cv.put("ProductId", productId);
					cv.put("CategoryId",cat.Id);
		
					if (db.insert("ProductHasCategories", null, cv) == -1)
						throw new Exception("Faile to insert to ProductHasCategories catId: " + cat.Id);
	 			}
	 		}
	 		
	 		for (int catId : categoryIds)
	 		{
	 			if (!addedCategories.contains(catId))
		 	     	try {
		 				if (db.delete("ProductHasCategories", "ProductId ="+productId + " and CategoryId =" + catId, null) == 0)
		 					throw new Exception("Failed to delete from ProductHasCategories ProductId: "+productId + " CategoryID "+catId);
		 	     	}
		 	     	catch(SQLException sqle) {
		 	     		Log.d(this.getClass().getName() ,sqle.getMessage());
		 	     		throw sqle;	 
		 	     	}
	 		}
			return result == 1 ? true : false;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     		return false;
     	}
	}
	
	public List<Tax> GetTaxList()
	{
     	try {
     		
     		String query = "SELECT _id"
     						+ " FROM Taxes";
     		Cursor c = db.rawQuery(query, null);
     		
     		List<Tax> result = new ArrayList<Tax>();
     		if (!c.moveToFirst())
     			return result;
     		
     		List<Integer> tmp = new ArrayList<Integer>();
     		while (!c.isAfterLast()){
     			tmp.add(c.getInt(c.getColumnIndex("_id")));
     			c.moveToNext();
     		}
     		c.close();
     		for (int id : tmp)
     			result.add(GetTax(id));	
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	
		return null;
	}
	
	public Tax GetTax(int taxId)
	{
     	try {
     		
     		String query = "SELECT _id, Value" 
							+ " FROM Taxes" 
							+ " WHERE _id = " + taxId;
     		Cursor c = db.rawQuery(query, null);
     		

     		if (!c.moveToFirst())
     			return null;
     		
     		Tax result = new Tax();
     		result.Id = c.getInt(c.getColumnIndex("_id"));
     		result.Value = c.getDouble(c.getColumnIndex("Value"));
     		  		
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	
		return null;
	}
	
	public boolean StoreTax(Tax tax)
	{
     	try {
     		int result;
			ContentValues cv = new ContentValues();

			cv.put("_id", tax.Id);
			cv.put("Value",tax.Value);
			
			result = db.update("Taxes", cv, "_id ="+tax.Id, null);
			if (result == 0)
				result = db.insert("Taxes", null, cv) != -1 ? 1:0;
			
			return result == 1 ? true : false;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch(Exception e){
     		Log.d(this.getClass().getName() ,e.getMessage());
     		return false;
     	}
	}
	
	public boolean RemoveTax(int taxId)
	{
     	try {
     		int result;
			result = db.delete("Taxes", "_id ="+taxId, null);
			return result == 1 ? true : false;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     		return false;
     	}
	}

	public List<Unit> GetUnitList()
	{
     	try {
     		
     		String query = "SELECT _id"
     						+ " FROM Units";
     		Cursor c = db.rawQuery(query, null);
     		
     		List<Unit> result = new ArrayList<Unit>();
     		if (!c.moveToFirst())
     			return result;
     		
     		List<Integer> tmp = new ArrayList<Integer>();
     		while (!c.isAfterLast()){
     			tmp.add(c.getInt(c.getColumnIndex("_id")));
     			c.moveToNext();
     		}
     		c.close();
     		for (int id : tmp)
     			result.add(GetUnit(id));
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	
		return null;
	}
	
	public Unit GetUnit(int unitId)
	{
     	try {
     		
     		String query = "SELECT _id, Name" 
							+ " FROM Units" 
							+ " WHERE _id = " + unitId;
     		Cursor c = db.rawQuery(query, null);
     		

     		if (!c.moveToFirst())
     			return null;
     		
     		Unit result = new Unit();
     		result.Id = c.getInt(c.getColumnIndex("_id"));
     		result.Name = c.getString(c.getColumnIndex("Name"));
     		  		
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
		return null;
	}
	
	public boolean StoreUnit(Unit unit)
	{
     	try {
     		int result;
			ContentValues cv = new ContentValues();

			cv.put("_id", unit.Id);
			cv.put("Name",unit.Name);
			
			result = db.update("Units", cv, "_id ="+unit.Id, null);
			if (result == 0)
				result = db.insert("Units", null, cv) != -1 ? 1:0;
			
			return result == 1 ? true : false;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     		return false;
     	}
	}
	
	public boolean RemoveUnit(int unitId)
	{
     	try {
     		int result;
			result = db.delete("Units", "_id ="+unitId, null);
			return result == 1 ? true : false;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     		return false;
     	}
	}
	
	public ArrayList<Category> GetProductCategories(int productId)
	{
     	try {
     		
     		String query = "SELECT CategoryId" 
							+ " FROM ProductHasCategories" 
							+ " WHERE ProductId = " + productId;
     		Cursor c = db.rawQuery(query, null);
     		
     		ArrayList<Category> result = new ArrayList<Category>();
     		if (!c.moveToFirst())
     			return result;
     		
     		List<Integer> tmp = new ArrayList<Integer>();
     		while (!c.isAfterLast()){
	     		tmp.add(c.getInt(c.getColumnIndex("CategoryId")));
	     		c.moveToNext();
     		}
     		c.close();
     		for (int id : tmp)
	     		result.add(GetCategory(id));	    		  		
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
		return null;
	}
	
	public List<Category> GetCategoryList()
	{
     	try {
     		
     		String query = "SELECT _id"
     						+ " FROM Categories";
     		Cursor c = db.rawQuery(query, null);
     		
     		List<Category> result = new ArrayList<Category>();
     		if (!c.moveToFirst())
     			return result;
     		
     		List<Integer> tmp = new ArrayList<Integer>();
     		while (!c.isAfterLast()){
     			tmp.add(c.getInt(c.getColumnIndex("_id")));
     			c.moveToNext();
     		}
     		c.close();
     		for (int id : tmp)
     			result.add(GetCategory(id));
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	
		return null;
	}
	
	public Category GetCategory(int categoryId)
	{
     	try {
     		
     		String query = "SELECT _id, ParentCategoryId, Name, Description" 
							+ " FROM Categories" 
							+ " WHERE _id = " + categoryId;
     		Cursor c = db.rawQuery(query, null);
     		

     		if (!c.moveToFirst())
     			return null;
     		
     		Category result = new Category();
     		result.Id = c.getInt(c.getColumnIndex("_id"));
     		result.Name = c.getString(c.getColumnIndex("Name"));
     		result.Description = c.getString(c.getColumnIndex("Description"));
     		result.ParentId = c.getInt(c.getColumnIndex("ParentCategoryId"));
     		    	    		  		
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	
		return null;
	}
	
	public boolean StoreCategory(Category category)
	{
     	try {
     		int result;
			ContentValues cv = new ContentValues();

			cv.put("_id", category.Id);
			cv.put("Name",category.Name);
			cv.put("Description", category.Description);
			cv.put("ParentCategoryId", category.ParentId);
			
			result = db.update("Categories", cv, "_id ="+category.Id, null);
			if (result == 0)
				result = db.insert("Categories", null, cv) != -1 ? 1:0;
			
			return result == 1 ? true : false;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     		return false;
     	}
	}
	
	public boolean RemoveCategory(int categoryId)
	{
     	try {
     		int result;
			result = db.delete("Categories", "_id ="+categoryId, null);
			return result == 1 ? true : false;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     		return false;
     	}
	}
	
	public ArrayList<ProductImage> GetProductImages(int productImageId)
	{
     	try {
     		
     		String query = "SELECT _id, ProductId, Path, Position" 
							+ " FROM ProductImage" 
							+ " WHERE _id = " + productImageId;
     		Cursor c = db.rawQuery(query, null);
     		
     		ArrayList<ProductImage> result = new ArrayList<ProductImage>();
     		if (!c.moveToFirst())
     			return result;
     		
     		
     		while (!c.isAfterLast()){
	     		ProductImage p = new ProductImage();
	     		p.Id = c.getInt(c.getColumnIndex("_id"));
	     		p.ProductId = c.getInt(c.getColumnIndex("ProductId"));
	     		p.Path = c.getString(c.getColumnIndex("Path"));
	     		p.Position = c.getInt(c.getColumnIndex("Position"));
	     		
	     		result.add(p);
	     		c.moveToNext();
     		}
     		    	    		  		
     		return result;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     	}
     	
		return null;
	}
	
	public boolean StoreProductImage(ProductImage productImage)
	{
     	try {
     		int result;
			ContentValues cv = new ContentValues();

			cv.put("_id", productImage.Id);
			cv.put("Path",productImage.Path);
			cv.put("Position", productImage.Position);
			cv.put("ProductId", productImage.ProductId);

			result = db.update("ProductImage", cv, "_id ="+productImage.Id, null);
			if (result == 0)
				result = db.insert("ProductImage", null, cv) != -1 ? 1 : 0;
			return result == 1 ? true : false;
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     		return false;
     	}
	}
	
	public boolean RemoveProductImage(int productImageId)
	{
     	try {
     		int result;
			result = db.delete("ProductImage", "_id ="+productImageId, null);
			return result == 1 ? true : false; 
     	}
     	catch(SQLException sqle) {
     		Log.d(this.getClass().getName() ,sqle.getMessage());
     		throw sqle;
     	}catch (Exception e){
     		Log.d(this.getClass().getName(), e.getMessage());
     		return false;
     	}
	}
}
